<?php
/**
 * Desc: 连接成功后的首页
 * Auth: Shaoming Lu<lushao1012@163.com>
 * Date: 2018/5/29
 * Time: 16:21
 */
namespace App\Http\Controllers;

use App\Http\Services\AjaxService;
use App\Http\Services\MCore;
use Illuminate\Http\Request;
use App\Http\Services\DB;

class IndexController extends BaseController
{
    /**
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @url     /index/index
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function index(Request $request)
    {
//        var_dump(session('db'));exit;
        $db_name = MCore::get_var('db_name', $request, 0);
        $table_name = MCore::get_var('table_name', $request, 0);
        $db = new DB();
        // 获取所有的数据库
        $db_list = $db->show_all_databases();


        $table_list = [
            'field' => [],
            'result' => [],
        ];
        if ($db_name) {
            $db1 = new DB($db_name);
            $table_list = $db1->get_all_tables_and_comment($db_name);
        }

//         var_dump($table_list);exit;

        return view('index.index', [
            'db_name' => $db_name,
            'db_list' => $db_list,
            'table_list' => $table_list,
            'total_table' => count($table_list),
        ]);
    }

    /**
     * 浏览数据列表
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @url     /index/record
     * @param Request $request
     */
    public function record(Request $request)
    {
        $db_name = MCore::get_var('db_name', $request, 0);
        $table_name = MCore::get_var('table_name', $request, 0);
        $sql = MCore::get_var('sql', $request, 0);

        $table_list = [
            'field' => [],
            'result' => [],
        ];
        if ($db_name) {
            $pdo = new DB($db_name);
            $table_list = $pdo->show_all_tables($db_name);
        }

        return view('index.record', [
            'tab' =>'record',
            'db_name' => $db_name,
            'table_name' => $table_name,
            'sql' => $sql,
            'table_list' => $table_list,
        ]);

    }

    /**
     * 获取字段列表
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param Request $request
     */
    public function getFieldsData(Request $request)
    {
        $db_name = MCore::get_var('db_name', $request, 0);
        $table_name = MCore::get_var('table_name', $request, 0);
        $pdo = new DB($db_name);
        $list = $pdo->get_table_structure($table_name);
        AjaxService::ajax_return(['msg'=>'SUCCESS'], $list);
    }

    /**
     * 获取数据列表
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param Request $request
     */
    public function getListData(Request $request)
    {
        $db_name = MCore::get_var('db_name', $request, 0);
        $table_name = MCore::get_var('table_name', $request, 0);
        $db = new DB($db_name);
        $field = '*';
        $order = '';
        $page = MCore::get_var('page', $request, 0);
        $limit = MCore::get_var('limit', $request, 0);
        $page = $page? $page: 1;
        $limit = $limit? $limit: 25;
        $list = $db->get_list($table_name, $field, $order, $page, $limit);
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
//        var_dump($list);exit;
        $total = $db->get_total_rows($table_name);
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        $total_page = ceil($total / $limit);

        $ret['page'] = $page;
        $ret['limit'] = $limit;
        $ret['total'] = $total;
        $ret['total_page'] = $total_page;
        $ret['list'] = $list;
        AjaxService::ajax_return(['msg'=>'SUCCESS'], $ret);
    }

    /**
     * 执行SQL并获取结果
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param Request $request
     */
    public function getSqlResult(Request $request)
    {
        $db_name = MCore::get_var('db_name', $request, 0);
        $table_name = MCore::get_var('table_name', $request, 0);
        $sql = MCore::get_var('sql', $request, 0);

        $db = new DB($db_name);
        $field = '*';
        $order = '';
        $page = MCore::get_var('page', $request, 0);
        $limit = MCore::get_var('limit', $request, 0);
        $page = $page? $page: 1;
        $limit = $limit? $limit: 25;
        $origin_list = [];
        $total = 0;
        $total_page = 1;
        if ($sql) {
            $origin_list = $db->query($sql);
            if ($db->get_error()) {
                AjaxService::ajax_return(['error' => $db->get_error()]);
            }
            $total = count($origin_list);

            $sql = strtolower($sql);
            $sql = trim($sql);
            if (substr($sql, 0, 6) == 'select') {
                if (strpos($sql, ' limit ') === FALSE) {
                    $sql = trim($sql);// 去除首尾空格
                    // 去除末尾分号字符（可能有多个）
                    $sql = rtrim($sql, ';');

                    $sql .= ' LIMIT '.(($page-1)*$limit).','.$limit;
                    $list = $db->query_result_list($sql);
                    $total_page = ceil($total / $limit);
                } else {
                    $limit = $total;
                    $total_page = 1;
                }
            } else {
                $list = $db->query_result_list($sql);

            }
            

            if ($db->get_error()) {
                AjaxService::ajax_return(['error' => $db->get_error()]);
            }

        } else {
            $list = $db->get_list($table_name, $field, $order, $page, $limit, 1);
            if ($db->get_error()) {
                AjaxService::ajax_return(['error' => $db->get_error()]);
            }
            $total = $db->get_total_rows($table_name);
            if ($db->get_error()) {
                AjaxService::ajax_return(['error' => $db->get_error()]);
            }
            $total_page = ceil($total / $limit);
        }


        $ret['page'] = $page;
        $ret['limit'] = $limit;
        $ret['total'] = $total;
        $ret['total_page'] = $total_page;
        $ret['list'] = $list;
        AjaxService::ajax_return(['msg'=>'SUCCESS'], $ret);

    }

    /**
     * 创建表
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function createTable(Request $request)
    {
        $db_name = MCore::get_var('db_name', $request, 0);
        $table_list = [
            'field' => [],
            'result' => [],
        ];
        if ($db_name) {
            $db1 = new DB($db_name);
            $table_list = $db1->show_all_tables();
        }
        return view('index.createTable',[
            'db_name' => $db_name,
            'table_list' => $table_list,
        ]);
    }

    /**
     * 结构
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function structure(Request $request)
    {
        $db_name = MCore::get_var('db_name', $request, 0);
        $table_name = MCore::get_var('table_name', $request, 0);

        $table_list = [
            'field' => [],
            'result' => [],
        ];

        $table_structure = [];
        $error = '';
        if ($db_name) {
            $pdo = new DB($db_name);
            $table_list = $pdo->show_all_tables($db_name);
            $table_structure = $pdo->get_table_structure($table_name);
            if ($table_structure === false) {
                $error = $pdo->get_error();
            }
        }
//        var_dump($table_structure);exit;

        return view('index.structure', [
            'tab' =>'structure',
            'db_name' => $db_name,
            'table_name' => $table_name,
            'table_list' => $table_list,
            'table_structure' => $table_structure,
            'error' => $error,
        ]);
    }

    /**
     * 字段编辑
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param Request $request
     */
    public function editField(Request $request)
    {
        $db_name = MCore::get_var('db_name', $request, 0);
        $table_name = MCore::get_var('table_name', $request, 0);
        $field_name = MCore::get_var('field_name', $request, 0);

        $table_list = [
            'field' => [],
            'result' => [],
        ];

        $field_data = [];
        $error = '';
        if ($db_name) {
            $pdo = new DB($db_name);
            $table_list = $pdo->show_all_tables($db_name);
            $field_data = $pdo->get_fields_data($table_name, $field_name);
            if (!$field_data) {
                $error = $db_name.'.'.$table_name.'表的字段'.$field_name.'不存在！';
            }
            if ($field_data) {
                $field_data = $field_data[0];
                // 处理字段长度
                $field_data['COLUMN_LENGTH'] = 0;
                $column_type = $field_data['COLUMN_TYPE'];
                $column_length = array();
                preg_match_all("/(?:\()(.*)(?:\))/i",$column_type, $column_length);
                if (isset($column_length[1][0])) {
                    $field_data['COLUMN_LENGTH'] = $column_length[1][0];
                }
            }
        }
//        var_dump($error);exit;

        return view('index.editField', [
            'tab' =>'structure',
            'db_name' => $db_name,
            'table_name' => $table_name,
            'field_name' => $field_name,
            'table_list' => $table_list,
            'field_data' => $field_data,
            'error' => $error,
        ]);
    }

    public function createField(Request $request)
    {
        $db_name = MCore::get_var('db_name', $request, 0);
        $table_name = MCore::get_var('table_name', $request, 0);
        $after = MCore::get_var('after', $request, 0);
        $pdo = new DB($db_name);
        $table_list = $pdo->show_all_tables($db_name);
        return view('index.createField', [
            'tab' =>'structure',
            'db_name' => $db_name,
            'table_name' => $table_name,
            'table_list' => $table_list,
            'after'      => $after,
        ]);
    }

    public function operation(Request $request)
    {
        $db_name = MCore::get_var('db_name', $request, 0);
        $table_name = MCore::get_var('table_name', $request, 0);

        $pdo = new DB($db_name);
        $table_list = $pdo->show_all_tables($db_name);
        $table_info = $pdo->get_table_info($table_name);
        return view('index.operation', [
            'tab' =>'operation',
            'db_name' => $db_name,
            'table_name' => $table_name,
            'table_list' => $table_list,
            'table_info' => $table_info,
        ]);
    }

    public function insertLine(Request $request)
    {
        $db_name = MCore::get_var('db_name', $request, 0);
        $table_name = MCore::get_var('table_name', $request, 0);

        $pdo = new DB($db_name);
        $table_list = $pdo->show_all_tables($db_name);

        $field_data = $pdo->get_fields_list($table_name);
//        var_dump($field_data);exit;
        return view('index.insertLine', [
            'tab' =>'operation',
            'db_name' => $db_name,
            'table_name' => $table_name,
            'table_list' => $table_list,
            'field_data' => $field_data,
        ]);
    }

    public function editTableLine(Request $request)
    {
        $db_name = MCore::get_var('db_name', $request, 0);
        $table_name = MCore::get_var('table_name', $request, 0);
        $data_id = MCore::get_var('data_id', $request, 0);

        $pdo = new DB($db_name);
        $table_list = $pdo->show_all_tables($db_name);

        $field_data = $pdo->get_fields_list($table_name);

        $data = $pdo->get_data_by_id($table_name, $data_id);
//        var_dump($data);exit;
        return view('index.editTableLine', [
            'tab' =>'operation',
            'db_name' => $db_name,
            'table_name' => $table_name,
            'table_list' => $table_list,
            'field_data' => $field_data,
            'data' => $data,
        ]);
    }

}